package net.kuratkoo.jidelnicekvut;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "jidelnicekvut";
    private static final int DATABASE_VERSION = 5;
    private static final String TAG = "JidelnicekVUT|DatabaseHelper";

    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE canteen (" +
                       "_id INTEGER PRIMARY KEY," +
                       "name VARCHAR NOT NULL COLLATE LOCALIZED," +
                       "guid VARCHAR NOT NULL COLLATE LOCALIZED," +
                       "valid INTEGER NOT NULL DEFAULT 1," +
                       "address VARCHAR NOT NULL COLLATE LOCALIZED)"
                  );
        db.execSQL("CREATE TABLE canteen_star (" +
                       "canteen_id INTEGER PRIMARY KEY," +
                       "star INTEGER NOT NULL DEFAULT 1)"
                  );
        db.execSQL("CREATE TABLE canteen_hours (" +
                       "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                       "canteen_id INTEGER NOT NULL," +
                       "day INTEGER NOT NULL," +
                       "time_from TIME NOT NULL," +
                       "time_to TIME NOT NULL," +
                       "FOREIGN KEY (canteen_id) REFERENCES canteen (_id))"
                  );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "Upgrading DB from " + oldVersion + " to " + newVersion);
        if (oldVersion == 1) {
            db.execSQL("DROP TABLE menza");
            this.onCreate(db);
            oldVersion = 4;
        }

        if (oldVersion == 2) {
            db.execSQL("DROP TABLE menza");
            db.execSQL("DROP TABLE menza_fav");
            db.execSQL("DROP TABLE menza_hours");
            this.onCreate(db);
            oldVersion = 4;
        }

        if (oldVersion == 3) {
            db.execSQL("CREATE TABLE food_vote (" +
                           "food_vote_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                           "canteen_id INTEGER NOT NULL," +
                           "food VARCHAR NOT NULL COLLATE LOCALIZED,"+
                           "vote INTEGER NOT NULL DEFAULT 0)"
                      );
            db.execSQL("ALTER TABLE menza RENAME TO canteen");
            db.execSQL("ALTER TABLE menza_star RENAME TO canteen_star");
            db.execSQL("ALTER TABLE menza_hours RENAME TO canteen_hours");

            /** ALTER TABLE canteen_star ALTER COLUMN menza_id RENAME TO canteen_id **/
            
            db.execSQL("ALTER TABLE canteen_star RENAME TO tmp_canteen_star");
            db.execSQL("CREATE TABLE canteen_star (" +
                       "canteen_id INTEGER PRIMARY KEY," +
                       "star INTEGER NOT NULL DEFAULT 1)");
            db.execSQL("INSERT INTO canteen_star (canteen_id, star) SELECT menza_id, star FROM tmp_canteen_star");
            db.execSQL("DROP TABLE tmp_canteen_star");
            
            /** ALTER TABLE canteen_hours ALTER COLUMN menza_id RENAME TO canteen_id **/
            
            db.execSQL("ALTER TABLE canteen_hours RENAME TO tmp_canteen_hours");
            db.execSQL("CREATE TABLE canteen_hours (" +
                       "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                       "canteen_id INTEGER NOT NULL," +
                       "day INTEGER NOT NULL," +
                       "time_from TIME NOT NULL," +
                       "time_to TIME NOT NULL," +
                       "FOREIGN KEY (canteen_id) REFERENCES canteen (_id))"
            );
            db.execSQL("INSERT INTO canteen_hours (_id, canteen_id, day, time_from, time_to) SELECT _id, menza_id, day, time_from, time_to FROM tmp_canteen_hours");
            db.execSQL("DROP TABLE tmp_canteen_hours");
            
            oldVersion = 4;
        }

        if (oldVersion == 4) {
            db.execSQL("DROP TABLE food_vote");
            oldVersion = 5;
        }
    }
}
